﻿using System.Collections.Generic;
using System.Data.SqlClient;
using System.Linq;
using Lottery.DBUtility;
using Lottery.Entity;
using System.Data;

namespace Lottery.DataAccess
{
    public class LotteryTeamController
    {
        private const string SELECT_ALLTEAM = "SELECT * FROM LotteryTeam";
        private const string CONDITION_BYTEAMID = " WHERE ID=" + PARM_ID;
        private const string CONDITION_BYTEAMNAME = " WHERE TeamName=" + PARM_TEAMNAME;
        private const string INSERT = "INSERT INTO LotteryTeam(TeamName) VALUES(" + PARM_TEAMNAME + ")";
        private const string CONDITION_UNDELETED = " WHERE IsDeleted = 0";

        private const string PARM_ID = "@ID";
        private const string PARM_TEAMNAME = "@TeamName";


        public int Insert(LotteryTeam lotteryTeam)
        {
            int result = 0;
            result = Insert(lotteryTeam.TeamName);
            return result;
        }

        public List<LotteryTeam> GetAllTeam()
        {
            return LotterySQLEntityHelper<LotteryTeam>.GetEntitySet(SELECT_ALLTEAM);
        }

        public List<LotteryTeam> GetAllUndeletedTeam()
        {
            return LotterySQLEntityHelper<LotteryTeam>.GetEntitySet(SELECT_ALLTEAM + CONDITION_UNDELETED);
        }

        public LotteryTeam GetTeamByID(int teamID)
        {
            return LotterySQLEntityHelper<LotteryTeam>.GetEntity(SELECT_ALLTEAM + CONDITION_BYTEAMID, 
                    new SqlParameter(PARM_ID,teamID));
        }

        public LotteryTeam GetTeamByName(string teamName)
        {
            return LotterySQLEntityHelper<LotteryTeam>.GetEntity(SELECT_ALLTEAM + CONDITION_BYTEAMNAME,
                    new SqlParameter(PARM_TEAMNAME, teamName));
        }

        public int Insert(string teamName)
        {
            int result = 0;
            if (GetTeamByName(teamName) == null)
            {
                result = LotterySQLHelper.ExecuteNonQuery(CommandType.Text, INSERT, new SqlParameter(PARM_TEAMNAME, teamName));
            }
            return result;
        }

        public int Insert(List<LotteryTeam> lotteryTeamSet)
        {
            int result = 0;

            IEnumerable<string> teamNameSet = lotteryTeamSet.Select(l => l.TeamName).Distinct();

            List<LotteryTeam> lotteryTeamInDB = GetAllUndeletedTeam();
            
            IEnumerable<string> teamNameSetInDB = lotteryTeamInDB.Select(l => l.TeamName);
            
            foreach (string teamName in teamNameSet)
            {
                if (!lotteryTeamInDB.Select(l => l.TeamName == teamName).Any())
                {
                    result += LotterySQLHelper.ExecuteNonQuery(CommandType.Text, INSERT, new SqlParameter(PARM_TEAMNAME, teamName));                    
                }
            }

            return result;
        }
    }
}
